﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Bamboo.Excel
{
    public static class NPOIEx
    {
        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="Sheet"></param>
        /// <param name="RowNo"></param>
        /// <param name="ColNo"></param>
        /// <param name="DefaultValue"></param>
        /// <returns></returns>
        public static string GetString(this ISheet Sheet, int RowNo, int ColNo, string DefaultValue)
        {
            string ResultText;
            if ((RowNo < Sheet.FirstRowNum) || (RowNo > Sheet.LastRowNum))
                return DefaultValue;

            IRow Row = Sheet.GetRow(RowNo);
            if (Row == null)
                return DefaultValue;
            ICell Cell = Row.GetCell(ColNo, MissingCellPolicy.CREATE_NULL_AS_BLANK);
            if (Cell == null)
                return DefaultValue;
            switch (Cell.CellType)
            {
                case CellType.Numeric:
                    ResultText = Convert.ToString(Cell.NumericCellValue);
                    break;
                case CellType.Boolean:
                    ResultText = Convert.ToString(Cell.BooleanCellValue);
                    break;
                case CellType.Formula:
                    ResultText = Cell.StringCellValue;
                    break;
                case CellType.Unknown:
                    ResultText = DefaultValue;
                    break;
                case CellType.Blank:
                    ResultText = DefaultValue;
                    break;
                case CellType.Error:
                    ResultText = DefaultValue;
                    break;
                default:
                    ResultText = Cell.StringCellValue;
                    break;
            }
            return ResultText.Trim();
        }

        public static XSSFWorkbook LoadXlsx(string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                WorkbookFactory.SetImportOption(ImportOption.TextOnly);
                XSSFWorkbook workbook = new XSSFWorkbook(fs);
                return workbook;
            }
        }

        public static HSSFWorkbook LoadXls(string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                WorkbookFactory.SetImportOption(ImportOption.TextOnly);
                HSSFWorkbook workbook = new HSSFWorkbook(fs);
                return workbook;
            }
        }

        public static IWorkbook LoadExcel(string fileName)
        {
            try
            {
                return LoadXlsx(fileName);
            }
            catch
            {
                return LoadXls(fileName);
            }
        }

        public static IWorkbook LoadExcelWithCopy(string filename)
        {
            FileInfo file = new FileInfo(filename);
            Random r = new Random();
            string tempfilename = "";
            do
            {
                tempfilename = "temp_read_" + r.Next(999999) + file.Extension;
            } while (File.Exists(tempfilename));
            //拷贝临时文件, 读取, 删除
            var tempfile = file.CopyTo(tempfilename);
            try
            {
                return LoadExcel(tempfile.FullName);
            }
            finally
            {
                tempfile.Delete();
            }
        }

        private static DataTable ToDataTable(this ISheet sheet, int startRow, int startCol,
            bool containHeader)
        {
            var table = new DataTable();
            IRow firstRow = sheet.GetRow(startRow);
            if (firstRow == null)
            {
                return table;
            }
            int lastCellNum = firstRow.LastCellNum;
            for (int i = startCol; i <= lastCellNum; i++)
            {
                if (containHeader)
                    table.Columns.Add(sheet.GetString(startRow, i, ""));
                else
                    table.Columns.Add(i.ToString());
            }
            for (int i = containHeader ? startRow + 1 : startRow; i <= sheet.LastRowNum; i++)
            {
                DataRow dr = table.NewRow();
                IRow row = sheet.GetRow(i);
                for (int j = startCol; j <= lastCellNum; j++)
                {
                    dr[j - startCol] = sheet.GetString(i, j, "");
                }

                table.Rows.Add(dr);
            }
            return table;
        }

        public static DataTable SheetToDataTable(string fileName, string sheetName, int startRow, int startCol, bool containHeader = true)
        {
            return LoadExcel(fileName).SheetToDataTable(sheetName, startRow, startCol, containHeader);

        }

        public static DataTable SheetToDataTable(this IWorkbook book, string sheetName, int startRow, int startCol, bool containHeader = true)
        {
            var sheet = book.GetSheet(sheetName);
            if (sheet == null)
            {
                throw new Exception("excel文档中找不到页面：" + sheetName);
            }
            return sheet.ToDataTable(startRow, startCol, containHeader);
        }

        public static void SetColumnsWidth(this ISheet sheet, params int[] widths)
        {
            for (int i = 0; i < widths.Length; i++)
            {
                sheet.SetColumnWidth(i, widths[i] * 256 + 64);
            }
        }

        public static IRow AddRow(this ISheet sheet, int rowIndex, params string[] values)
        {
            IRow row = sheet.CreateRow(rowIndex);
            AddCells(row, values);
            return row;
        }

        public static void AddCells(this IRow row, params string[] values)
        {
            foreach (string value in values)
            {
                ICell cell = row.CreateCell(row.Cells.Count, CellType.String);
                cell.SetCellValue(value);
            }
        }

        public static void ListToSheet<T>(IEnumerable<T> list, string filename, string sheetName)
            where T : class
        {
            var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
            var sheet = workbook.CreateSheet(sheetName);
            ListToSheet(sheet, list);
            using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write, FileShare.Read, 128*1024))
            {
                workbook.Write(fs);
            }
        }

        public static void ListToSheet<T>(this ISheet sheet, IEnumerable<T> objectList)
            where T : class
        {
            int rowIndex = sheet.PhysicalNumberOfRows;
            Type objectType = typeof(T);
            var columns = Bamboo.Utils.SerializeFieldInfo.Parse(objectType);

            sheet.DefaultRowHeight = 400;
            //写入首行标题
            if (rowIndex == 0)
            {
                List<string> colNames = new List<string>(32);
                List<int> colWidths = new List<int>(32);
                for (int index = 0; index < columns.Count; ++index)
                {
                    var column = columns[index];
                    string name = column.Name();
                    int defWidth = Encoding.Default.GetBytes(name).Length;
                    int width = column.Width > 0 ? column.Width : defWidth;
                    colNames.Add(name);
                    colWidths.Add(width);
                }
                sheet.SetColumnsWidth(colWidths.ToArray());
                sheet.AddRow(rowIndex++, colNames.ToArray());
                /*
                 * 设置首行颜色
                IRow firstRow = sheet.GetRow(0);
                for (int j = 0; j < attList.Count; j++)
                {
                    ICell cell = firstRow.GetCell(j, MissingCellPolicy.RETURN_NULL_AND_BLANK);
                    cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
                }
                */
            }
            //写入每一行数据
            foreach (T obj in objectList)
            {
                var row = sheet.AddRow(rowIndex++);
                for (int columnIndex = 0; columnIndex < columns.Count; ++columnIndex)
                {
                    var column = columns[columnIndex];
                    var fieldtype = column.FieldType().Name.ToLower();
                    if (column.IsNumericType)
                    {
                        object valueObject = column.GetValue(obj);
                        string valueString = (valueObject != null) ? valueObject.ToString() : "0";
                        double valueDouble = double.Parse(valueString);
                        XSSFCell cell = row.CreateCell(columnIndex, CellType.Numeric) as XSSFCell;
                        cell.SetCellValue(valueDouble);
                    }
                    else
                    {
                        object valueObject = column.GetValue(obj);
                        string valueString = (valueObject != null) ? valueObject.ToString() : "";
                        XSSFCell cell = row.CreateCell(columnIndex, CellType.String) as XSSFCell;
                        cell.SetCellValue(valueString);
                    }
                }
            }
        }

        public static void SheetToList<T>(string fileName, string sheetName, IList<T> list) where T : new()
        {
            IWorkbook book = LoadExcel(fileName);
            book.SheetToList<T>(sheetName, list);
        }

        public static void SheetToList<T>(this IWorkbook book, string sheetName, IList<T> list) where T : new()
        {
            ISheet sheet;
            if (string.IsNullOrWhiteSpace(sheetName))
                sheet = book.GetSheetAt(0);
            else
                sheet = book.GetSheet(sheetName);
            //找不到默认取第一页
            if (sheet == null)
                sheet = book.GetSheetAt(0);

            if (sheet == null)
            {
                throw new Exception("excel文档中找不到页面：" + sheetName);
            }
            FillList(sheet, list);
        }

        public static void FillList<T>(this ISheet sheet, IList<T> list) where T : new()
        {
            Type entityType = typeof(T);
            var memberAttList = Bamboo.Utils.SerializeFieldInfo.Parse(entityType);

            int rowCount = sheet.LastRowNum - sheet.FirstRowNum + 1;

            if (rowCount < 2)
            {
                return;
            }
            //从sheet页第一行，找到col与对象字段的对应关系
            //以字段为基准查找
            IRow firstRow = sheet.GetRow(sheet.FirstRowNum);
            if (firstRow == null)
                return;
            foreach (var memberAtt in memberAttList)
            {
                string columnName = memberAtt.Name();
                ICell columnCell = firstRow.Cells.Find(a => a.StringCellValue == columnName);
                if (columnCell == null)
                {
                    throw new Exception("在[" + sheet.SheetName + "]页面中找不到[" + columnName + "]列");
                }
                memberAtt.RealColumnIndex = columnCell.ColumnIndex;
            }
            //从sheet页第二行开始读取列表值
            for (int rowIndex = sheet.FirstRowNum + 1; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                T entity = new T();
                foreach (var memberAtt in memberAttList)
                    try
                    {
                        string valueString = sheet.GetString(rowIndex, memberAtt.RealColumnIndex, "").Trim();
                        var fieldType = memberAtt.FieldType();
                        if (fieldType == typeof(string))
                        {
                            memberAtt.SetValue(entity, valueString);
                        }
                        else if (fieldType == typeof(int))
                        {
                            if (string.IsNullOrWhiteSpace(valueString))
                            {
                                memberAtt.SetValue(entity, (int)0);
                            }
                            else
                            {
                                int value = int.Parse(valueString);
                                memberAtt.SetValue(entity, value);
                            }
                        }
                        else if (fieldType == typeof(uint))
                        {
                            if (string.IsNullOrWhiteSpace(valueString))
                            {
                                memberAtt.SetValue(entity, (uint)0);
                            }
                            else
                            {
                                uint value = uint.Parse(valueString);
                                memberAtt.SetValue(entity, value);
                            }
                        }
                        else if (fieldType == typeof(double))
                        {
                            if (string.IsNullOrWhiteSpace(valueString))
                            {
                                memberAtt.SetValue(entity, (double)0f);
                            }
                            else
                            {
                                double value = double.Parse(valueString);
                                memberAtt.SetValue(entity, value);
                            }
                        }
                        else if (fieldType == typeof(bool))
                        {
                            string valueLower = valueString.ToLower();
                            if (valueString == "是" || valueString == "1" || valueLower == "y" || valueLower == "true")
                            {
                                memberAtt.SetValue(entity, true);
                            }
                            else
                            {
                                memberAtt.SetValue(entity, false);
                            }
                        }
                        else if (fieldType.BaseType == typeof(Enum))
                        {
                            Enum value = (Enum)Enum.Parse(fieldType, valueString, true);
                            memberAtt.SetValue(entity, value);
                        }
                        else
                        {
                            throw new Exception("不支持该字段[" + memberAtt.Name() + "]类型[" + fieldType.Name + "]的解析");
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("解析Excel字段失败，页面=" + sheet.SheetName + ", 行=" + rowIndex + ", 列=" + memberAtt.Name(), ex);
                    }
                list.Add(entity);
            }

        }
    }
}
